package cn.edu.hhu.DAO.impl;

import cn.edu.hhu.Bean.Course;
import cn.edu.hhu.Bean.Student;
import cn.edu.hhu.DAO.StudentDao;
import cn.edu.hhu.util.JDBCUtils;
import org.junit.Test;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @anthor: lddd
 * @date: 2020/5/8 - 9:11
 * @description:
 **/
public class StudentDaoImpl implements StudentDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    /**
     * 根据学号和密码查询学生
     * @param sid
     * @param pwd
     * @return 学生信息
     */
    @Override
    public Student findStudentByIdAndPassword(String sid, String pwd) {
        try {
            String sql = "select * from student_info where sid = ? and pwd = ?";
            Student student = template.queryForObject(sql, new BeanPropertyRowMapper<>(Student.class), sid, pwd);
            return student;
        } catch (Exception e) {
            return null;
        }
    }

    /**
     * 根据学号修改学生信息
     * @param student
     * @return 0表示失败，1表示成功
     */
    @Override
    public int modifyStudentByID(Student student) {
        String sql = "update student_info set email = ? , tel = ? ,pwd = ? ,activation = ? where  sid = ?";
        int update = template.update(sql, student.getEmail(), student.getTel(), student.getPwd(), student.isActivation(), student.getSid());
        return update;
    }

    /**
     * 根据学号和姓名查询学生
     * @param sid
     * @return 学生信息
     */
    @Override
    public Student findStudentByIdAndName(String sid, String sname) {
        try {
            String sql = "select * from student_info where sid = ? and sname = ?";
            Student student = template.queryForObject(sql, new BeanPropertyRowMapper<>(Student.class), sid, sname);
            return student;
        } catch (Exception e) {
            return null;
        }
    }

    /**
     * 查询所有学生
     * @param
     * @return 学生列表
     */
    @Override
    public List<Student> findAllStudent() {
        String sql = "SELECT * FROM student_info";
        List<Student> list = template.query(sql, new BeanPropertyRowMapper<>(Student.class));
        return list;
    }

    /**
     * 通过sid删除学生
     * @param sid
     * @return 是否成功
     */
    @Override
    public boolean deleteStudentByID(String sid) {
        try {
            String sql = "DELETE FROM student_info WHERE sid = ?";
            int update = template.update(sql, sid);
            if (update == 1) return true;
            else return false;
        } catch (Exception e) {
            return false;
        }
    }

    /**
     * 注册一条学生信息
     * @param student
     * @return 是否成功
     */
    @Override
    public boolean insertSingleStudent(Student student) {
        try {
            //activation默认为0
            String sql = "INSERT INTO student_info(sid,sname,activation) VALUES (?,?,0)";
            int update = template.update(sql, student.getSid(), student.getSname());
            if (update == 1) return true;
            else return false;
        } catch (Exception e) {
            return false;
        }
    }

    /**
     * 批量注册多条学生
     * @param students
     */
    @Override
    public void batchInsertStudent(List<Student> students) {
        //activation默认为0
        String sql = "INSERT IGNORE INTO student_info(sid,sname,activation) VALUES (?,?,0)";
        int[] ints = template.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                preparedStatement.setString(1, students.get(i).getSid());
                preparedStatement.setString(2, students.get(i).getSname());
            }

            @Override
            public int getBatchSize() {
                return students.size();
            }
        });
    }

    @Override
    public boolean changeStudentPwd(String sid, String newPwd) {
        String sql = "update student_info set pwd = ? where  sid = ?";
        return template.update(sql, newPwd, sid) == 1;
    }


    @Test
    public void test1() {
        System.out.println("123".compareToIgnoreCase(null));
    }
}
